const express = require("express");
const multer = require("multer");
const router = express.Router();
const path = require("path");
const bcrypt = require("bcryptjs");
const { generateUniqueId, fileUrl } = require("../public/javascripts/index");
const db = require("../db");

router.post("/add", (req, res) => {
  // 执行定义好的登录函数
  const info = req.body;
  // 定义 SQL 语句
  let sql = "insert into selection_plan set ?";
  db.query(
    sql,
    {
      planId: generateUniqueId(),
      planName: info.planName,
      planDate: info.planDate,
      status: 0,
      createdTime: Date.now(),
      remark: info.remark,
    },
    (error, result) => {
      if (error) return res.cc(error);
      // 判断影响行数是否为 1
      if (result.affectedRows !== 1) return res.cc("新增失败！");
      return res.cc("新增成功", true);
    }
  );
});

router.delete("/delete", (req, res) => {
  const info = req.body;

  // 对客户端的数据进行校验
  if (!info.planId) {
    return res.send({
      success: false,
      message: "编码不能为空",
    });
  }

  // 删除 selection_plan 表中的数据
  const sqlDeletePlan = "DELETE FROM selection_plan WHERE planId=?";
  db.query(sqlDeletePlan, [info.planId], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.affectedRows !== 1) {
      return res.cc("未找到该记录或删除失败！");
    }

    // 删除 selection_history 表中与 planId 相关联的数据
    const sqlDeleteHistory = "DELETE FROM selection_history WHERE planId=?";
    db.query(sqlDeleteHistory, [info.planId], (error, result) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc("删除成功", true);
    });
  });
});

router.post("/list", (req, res) => {
  const {
    page = 1,
    limit = 10,
    planName = "",
    checkMan1 = "",
    checkMan2 = "",
    planDate = "",
  } = req.body;
  const offset = (page - 1) * limit;

  // 构建基础查询和参数
  let baseQuery = " FROM selection_plan WHERE 1=1";
  let params = [];

  if (planName) {
    baseQuery += " AND planName LIKE ?";
    params.push(`%${planName}%`);
  }
  if (checkMan1) {
    baseQuery += " AND checkMan1 LIKE ?";
    params.push(`%${checkMan1}%`);
  }
  if (checkMan2) {
    baseQuery += " AND checkMan2 LIKE ?";
    params.push(`%${checkMan2}%`);
  }
  if (planDate && planDate.length === 2 && planDate[0] && planDate[1]) {
    const startDate = new Date(planDate[0]);
    const endDate = new Date(planDate[1]);
    // 转换为 YYYY-MM-DD 格式
    const startDateStr = startDate.toISOString().split("T")[0];
    const endDateStr = endDate.toISOString().split("T")[0];
    baseQuery += ` AND planDate >= ? AND planDate <= ?`;
    params.push(startDateStr, endDateStr);
  }
  // 构建 SQL 计数查询语句
  const sqlCount = `SELECT COUNT(*) AS total ${baseQuery}`;

  // 查询总条数
  db.query(sqlCount, params, (error, countResults) => {
    if (error) {
      return res.cc(error);
    }

    const total = countResults[0].total;

    // 构建 SQL 查询语句
    let sql = `SELECT * ${baseQuery} LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    // 执行查询
    db.query(sql, params, (error, results) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc("获取记录成功", true, results, {
        total,
        page,
        limit,
      });
    });
  });
});

const updatePlan = (info, res) => {
  // 对客户端的数据进行校验
  if (!info.planId) {
    return res.send({
      success: false,
      message: "编码不能为空",
    });
  }

  // 获取现有记录
  const selectSQL = "SELECT * FROM selection_plan WHERE planId=?";
  db.query(selectSQL, [info.planId], (selectError, selectResult) => {
    if (selectError) {
      return res.cc(selectError);
    }

    if (selectResult.length === 0) {
      return res.cc("未找到该记录!");
    }

    // 更新库信息，保留现有值，更新新值
    const existingPlan = selectResult[0];
    const updateSQL =
      "UPDATE selection_plan SET " +
      "planName=?, " +
      "planDate=?, " +
      "checkMan1=?, " +
      "checkTime1=?, " +
      "checkMan2=?, " +
      "checkTime2=?, " +
      "status=?, " +
      "remark=? " +
      "WHERE planId=?";

    const updateValues = [
      info.planName || existingPlan.planName,
      info.planDate || existingPlan.planDate,
      info.checkMan1 || existingPlan.checkMan1,
      info.checkTime1 || existingPlan.checkTime1,
      info.checkMan2 || existingPlan.checkMan2,
      info.checkTime2 || existingPlan.checkTime2,
      info.status || existingPlan.status,
      info.remark || existingPlan.remark,
      info.planId,
    ];

    db.query(updateSQL, updateValues, (updateError, updateResult) => {
      if (updateError) {
        return res.cc(updateError);
      }

      if (updateResult.affectedRows !== 1) {
        return res.cc("修改失败！");
      }

      db.query(
        selectSQL,
        [info.planId],
        (finalSelectError, finalSelectResult) => {
          if (finalSelectError) {
            return res.cc(finalSelectError);
          }

          if (finalSelectResult.length === 0) {
            return res.cc("未找到更新后的记录!");
          }

          const updatedPlan = finalSelectResult[0];
          const newStatus =
            updatedPlan.checkTime1 && updatedPlan.checkTime2
              ? "1"
              : updatedPlan.status;

          if (newStatus !== updatedPlan.status) {
            const finalUpdateSQL =
              "UPDATE selection_plan SET status=? WHERE planId=?";
            db.query(
              finalUpdateSQL,
              [newStatus, info.planId],
              (finalUpdateError, finalUpdateResult) => {
                if (finalUpdateError) {
                  return res.cc(finalUpdateError);
                }

                if (finalUpdateResult.affectedRows !== 1) {
                  return res.cc("状态更新失败！");
                }

                return res.cc("修改成功", true);
              }
            );
          } else {
            return res.cc("修改成功", true);
          }
        }
      );
    });
  });
};

router.post("/check", (req, res) => {
  // 接收表单的数据
  const userInfo = req.body;
  // 定义 SQL 语句
  const sql = "select * from selection_user where userName=?";
  // 执行 SQL 语句，根据用户名查询用户的信息
  db.query(sql, userInfo.userName, (err, result) => {
    // 执行 SQL 语句失败
    if (err) return res.cc(err);
    // 执行 SQL 语句成功,但是获取的数据条数不为1 也是失败的
    if (result.length !== 1) return res.cc("验证失败！");
    // 经过上方俩条判断条件，则证明执行 SQL 成功
    // TODO ：判断密码是否正确
    const comRes = bcrypt.compareSync(userInfo.password, result[0].password);
    if (!comRes) return res.cc("验证失败");
    const responseRes = { planId: userInfo.planId };
    // 2 "监督员"  3 操作员"
    if (result[0].role === "2") {
      responseRes.status = "2";
      responseRes.checkMan2 = result[0].userName;
      responseRes.checkTime2 = Date.now();
    } else if (result[0].role === "3") {
      responseRes.status = "3";
      responseRes.checkMan1 = result[0].userName;
      responseRes.checkTime1 = Date.now();
    }

    updatePlan(responseRes, res);
  });
});

router.put("/update", (req, res) => {
  const info = req.body;
  updatePlan(info, res);
});

router.get("/findById", (req, res) => {
  const info = req.query;
  //对客户端的数据进行校验
  if (!info.planId) {
    return res.send({
      success: false,
      message: "编码不能为空",
    });
  }
  // 执行定义好的登录函数
  let sql = "select * from selection_plan where planId=?";
  db.query(sql, [info.planId], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该记录!");
    }
    return res.cc("获取详情成功", true, result[0]);
  });
});

module.exports = router;
